#!/bin/bash
# Author: fanyongfeng
# 安装mysql5.7
# 操作系统：centos7/银河麒麟v10-sp2

# 使用root用户执行
[[ $(id -u) -ne 0 ]] && echo "请使用root用户执行此脚本" && exit 1

usage(){
    echo "Usage:"
    echo "sh $0
            --prefix=安装目录(默认/data/mysql-\$VERSION)
            --version=MySQL版本(版本号需为5.7.x)
            --role=master|slave(默认master)
        "
    echo "示例:"
    echo "sh $0 --version=5.7.35 --role=master"
    exit 1
}

#处理参数，规范化参数
ARGS=$(getopt -o h --long prefix:,version:,role:,help -n "$0" -- "$@")

if [[ $? != 0 || $# -eq 0 ]]
then
        usage
fi

#重新排列参数顺序
eval set -- "${ARGS}"
#通过shift和while循环处理参数
while true
do
    case $1 in
        --prefix)
            PREFIX=$2
            shift 2
            ;;
        --version)
            VERSION=$2
            shift 2
            ;;
        --role)
            ROLE=$2
            shift 2
            ;;
        -h|-help)
            usage
            exit 1
            ;;
        --)
           shift
           break
           ;;
        *)
           echo "Internal error!"
           exit 1
           ;;
    esac
done

VERSION=${VERSION:-5.7.35}
[[ "$(echo ${VERSION} | awk -F'.' '{print $1$2}')" -ne 57 ]] && echo "主版本号必须为5.7" && exit 1
MAIN_VERSION=$(echo ${VERSION} | awk -F'.' '{print $1"."$2}')

INSTALL_DIR="${PREFIX:-/data/mysql-${VERSION}}"
DATA_DIR="${INSTALL_DIR}/data"
ROLE=${ROLE:-master}
[[ ${ROLE} == "master" ]] && SERVER_ID=1 || SERVER_ID=2
RUN_USER="mysql"
HOST_IP=$(ip a | grep global | head -1 | awk -F'/' '{print $1}' | awk '{print $2}')

# 关闭防火墙和selinux
systemctl stop firewalld && systemctl disable firewalld
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
setenforce  0

# 使用available内存的60%分配innodb_buffer_pool_size
INNODB_BUFFER_POOL_SIZE=$(free -g | sed -n '2p'| awk '{print int($7 * 0.6)}')
[[ ${INNODB_BUFFER_POOL_SIZE} -eq 0 ]] && INNODB_BUFFER_POOL_SIZE=1

# 安装依赖
echo "正在安装依赖..."
yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget libnuma* bzip2 xz

# 下载安装包
if [[ ! -f mysql-${VERSION}-el7-x86_64.tar.gz ]]
then
    echo "正在下载安装包..."
    wget -4 https://cdn.mysql.com/archives/mysql-${MAIN_VERSION}/mysql-${VERSION}-el7-x86_64.tar.gz
    if [[ $? -ne 0 ]]
    then
        echo "mysql-${VERSION}-el7-x86_64.tar.gz下载失败，请重试或手动上传此安装包"
        exit 1
    fi
fi

echo "正在解压安装包..."
mkdir -p $(echo ${INSTALL_DIR%/*})
tar zxf mysql-${VERSION}-el7-x86_64.tar.gz && mv mysql-${VERSION}-el7-x86_64 ${INSTALL_DIR}

mkdir -p ${INSTALL_DIR}/logs
mkdir -p ${DATA_DIR}
touch ${INSTALL_DIR}/logs/{slow.log,error.log}

# 创建用户、授权
useradd ${RUN_USER} -s /sbin/nologin
chown -R ${RUN_USER}.${RUN_USER} ${DATA_DIR} ${INSTALL_DIR}

# 添加环境变量
echo "export PATH=\$PATH:${INSTALL_DIR}/bin" > /etc/profile.d/mysql.sh
source /etc/profile

# 备份原 /etc/my.cnf
if [ -f /etc/my.cnf ]; then
    \cp /etc/my.cnf /etc/my.cnf.`date '+%Y%m%d%H%m'`
fi
# 添加配置文件
cat > /etc/my.cnf <<EOF
[client]
port=3306
socket=${DATA_DIR}/mysql.sock

[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
bind-address=${HOST_IP}
port=3306
user=${RUN_USER}
skip-name-resolve
basedir=${INSTALL_DIR}
datadir=${DATA_DIR}
socket=${DATA_DIR}/mysql.sock
pid-file = ${DATA_DIR}/mysql.pid
character-set-server=utf8mb4
# 集群中server-id不能相同
server-id=${SERVER_ID}
gtid-mode=ON
enforce-gtid-consistency=ON
 
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=3
# 中继日志
relay-log = relay-bin
relay-log-index = relay-bin.index
# 双主配置
# 另一台服务器设置为2
# auto-increment-offset = 1
# auto-increment-increment = 2
 
# 表名大小写敏感
lower_case_table_names = 0
# 参数优化
open_files_limit=65535
back_log=500
max_connections=1000
max_connect_errors=1000
max_allowed_packet=16M
thread_cache_size=300
key_buffer_size=256M
table_open_cache=1024
query_cache_type=1
query_cache_size=128M
join_buffer_size=8M
sort_buffer_size=8M
read_buffer_size=2M
read_rnd_buffer_size=2M
binlog_cache_size=12M
max_binlog_size=512M
innodb_buffer_pool_size=${INNODB_BUFFER_POOL_SIZE}G
innodb_log_file_size=512M
innodb_log_buffer_size=32M
 
# 控制MySQL磁盘写入策略以及数据安全性
sync_binlog=1
innodb_flush_log_at_trx_commit=1
 
# 主从复制，忽略部分库
# replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=sys

# 审计日志
general_log=1
general_log_file=${INSTALL_DIR}/logs/general.log
 
# 慢查询日志
slow_query_log=1
long_query_time=3
slow_query_log_file=${INSTALL_DIR}/logs/slow.log
# 错误日志
log-error=${INSTALL_DIR}/logs/error.log

[mysqldump]
quick
max_allowed_packet=32M

[mysqld_safe]
open-files-limit=65535
EOF

chown mysql.mysql /etc/my.cnf
 
# 初始化MySQL
echo "正在初始化MySQL..."
${INSTALL_DIR}/bin/mysqld --defaults-file=/etc/my.cnf --user=${RUN_USER} --basedir=${INSTALL_DIR} \
--datadir=${DATA_DIR} --initialize-insecure
[[ $? -ne 0 ]] && echo "mysql初始化失败" && rm -rf ${DATA_DIR} && exit 1
 
# 使用systemd管理mysql
cat > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
After=network.target
After=syslog.target

[Service]
User=${RUN_USER}
Group=${RUN_USER}
ExecStart=${INSTALL_DIR}/bin/mysqld_safe --datadir=${INSTALL_DIR}/data --pid-file=${INSTALL_DIR}/mysqld.pid
Restart=on-failure
RestartSec=30s

[Install]
WantedBy=multi-user.target
EOF

# 启动mysql
systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld
sleep 5
systemctl status mysqld
[[ $? -ne 0 ]] && echo "mysql启动失败" && exit 1

# 修改root密码
# 生成12个字符长的随机密码
PASSWORD="$(gpg --gen-random --armor 1 12)"
SQL="
    update user set authentication_string=password('${PASSWORD}') where user='root';
    flush privileges;
"
mysql -uroot mysql -e"${SQL}"
echo "MySQL root用户密码: ${PASSWORD}"

# 主从同步配置
if [[ ${ROLE} == "master" ]]
then
    REPL_PASSWORD="$(gpg --gen-random --armor 1 12)"
    # 主库上执行： 添加主从同步账号;
    MASTER_SQL="
        GRANT REPLICATION SLAVE,REPLICATION CLIENT on *.* to 'repl'@'%' identified by '${REPL_PASSWORD}';
        FLUSH PRIVILEGES;
    "
    mysql -uroot mysql -p"${PASSWORD}" -e"${MASTER_SQL}"
    # 主库上执行：查看master状态，记录下log-file名称和log_pos位置;
    # show master status\G;
    MASTER_LOG_FILE=$(mysql -uroot -p"${PASSWORD}" -e "show master status" | tail -1 | awk '{print $1}')
    MASTER_LOG_POS=$(mysql -uroot -p"${PASSWORD}" -e "show master status" | tail -1 | awk '{print $2}')

    # 从库上执行
    SLAVE_SQL="
        change master to master_host='${HOST_IP}',master_port=3306,master_user='repl',master_password='${REPL_PASSWORD}',master_log_file='${MASTER_LOG_FILE}',master_log_pos=${MASTER_LOG_POS};
        start slave;
        show slave status\G;
    "
    echo "主从同步账号密码：repl / ${REPL_PASSWORD}"
    echo "如需配置主从同步，请在从库上执行如下命令:"
    echo ${SLAVE_SQL}
fi

# 配置mysql密码复杂度
SQL="
    INSTALL PLUGIN validate_password SONAME 'validate_password.so';
    INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
    install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.so';
    -- 密码复杂度;
    set global validate_password_length = 10;
    set global validate_password_policy = 1;

    -- 登陆失败次数限制;
    set global connection_control_failed_connections_threshold = 5;
    -- 限制重试时间，此处为毫秒;
    set global connection_control_min_connection_delay=1000;
    set global connection_control_max_connection_delay=3000;
"
mysql -uroot mysql -p"${PASSWORD}" -e"${SQL}"
## 关闭密码验证插件
#SQL="
#    set global validate_password = off;
#"
#mysql -uroot mysql -p"${PASSWORD}" -e"${SQL}"
